<?php

defined('SYSPATH') or die('Access error!');


define('XW_VERSION', 'V1.0.0');
define('OBJECT', 'OBJECT', true);
define('ARRAY_A', 'ARRAY_A', true);
define('ARRAY_N', 'ARRAY_N', true);

class Database
{
    /* 数据库所需要的一些常量 */

    const XW_VERSION = 'V1.0.0';
    const OBJECT = 'OBJECT';
    const ARRAY_A = 'ARRAY_A';
    const ARRAY_N = 'ARRAY_N';

    /* 数据库所需要的错误提示 */

    private $xw_mysql_str = array
        (
        1 => '输入数据库的账号密码',
        2 => '请输入正确的数据库资料?',
        3 => '需要选择一个数据库',
        4 => 'mysql 没有连接正确',
        5 => '试图连接数据库中......'
    );
    public static $default = 'default';
    public static $instances = array();
    public $trace = false;  // 跟踪错误
    public $debug_all = false;
    public $debug_called = false;
    public $vardump_called = false;
    public $show_errors = true;
    public $num_queries = 0;
    public $last_query = null;
    public $last_error = null;
    public $col_info = null;
    public $captured_errors = array();
    public $cache_dir = FALSE;
    public $cache_queries = FALSE;
    public $cache_inserts = FALSE;
    public $use_disk_cache = FALSE;
    public $cache_timeout = 24; // 按照小时计算
    public $timers = array();
    public $total_query_time = 0;
    public $db_connect_time = 0;
    public $trace_log = array();
    public $use_trace_log = false;
    public $num_rows = 0;
    public $insert_id = 0;
    public $sql_log_file = false;
    public $do_profile = false;
    public $char_name = 'utf8';
    public $profile_times = array();
    public $debug_echo_is_on = true;     // 开启echo 输出debug

    public static function instance($name = NULL, array $config = NULL)
    {
        if ($name === NULL)
        {
            $name = Database::$default;
        }

        if (!isset(Database::$instances[$name]))
        {
            if ($config === NULL)
            {
                $config = Core::config('database');
            }
            $class = __CLASS__;
            Database::$instances[$name] = new $class($config[$name]);
        }

        return Database::$instances[$name];
    }

    function __construct($config)
    {
        $this->dbuser = $config['username'];
        $this->dbpassword = $config['password'];
        $this->dbname = $config['database'];
        $this->dbhost = $config['hostname'];
        $this->dbprefix = $config['table_prefix'];
        if (isset($config['char_name']))
        {
            $this->char_name = $config['char_name'];
        }

        if (isset($config['cache_dir']))
        {
            $this->cache_dir = $config['cache_dir'];
            $this->cache_queries = TRUE;
            $this->cache_inserts = TRUE;
            $this->use_disk_cache = TRUE;
        }
    }

    /**
     *  快速链接数据库
     * 
     * @param type $dbuser
     * @param type $dbpassword
     * @param type $dbname
     * @param type $dbhost
     * @return boolean
     */
    function quick_connect($dbuser = '', $dbpassword = '', $dbname = '', $dbhost = 'localhost')
    {
        $return_val = false;

        if (!$this->connect($dbuser, $dbpassword, $dbhost, true))
            ;
        else if (!$this->select_db($dbname))
            ;
        else
            $return_val = true;
        return $return_val;
    }

    /**
     * 正常链接数据库
     * 
     * @global type $xw_mysql_str
     * @param type $dbuser
     * @param type $dbpassword
     * @param type $dbhost
     * @return boolean
     */
    function connect($dbuser = '', $dbpassword = '', $dbhost = 'localhost')
    {
        $xw_mysql_str = $this->xw_mysql_str;
        $return_val = false;

        $this->timer_start('db_connect_time');

        if (!$dbuser)
        {
            $this->register_error($xw_mysql_str[1] . ' in ' . __FILE__ . ' on line ' . __LINE__);
            $this->show_errors ? trigger_error($xw_mysql_str[1], E_USER_WARNING) : null;
        }
        else if (!$this->dbh = @mysql_connect($dbhost, $dbuser, $dbpassword, true, 131074))
        {
            $this->register_error($xw_mysql_str[2] . ' in ' . __FILE__ . ' on line ' . __LINE__);
            $this->show_errors ? trigger_error($xw_mysql_str[2], E_USER_WARNING) : null;
        }
        else
        {
            $this->dbuser = $dbuser;
            $this->dbpassword = $dbpassword;
            $this->dbhost = $dbhost;
            $return_val = true;
        }
        $this->set_char($this->char_name);
        return $return_val;
    }

    /**
     *  选择数据库
     * @global type $xw_mysql_str
     * @param type $dbname
     * @return boolean
     */
    function select_db($dbname = '')
    {
        $xw_mysql_str = $this->xw_mysql_str;
        $return_val = false;

        if (!$dbname)
        {
            $this->register_error($xw_mysql_str[3] . ' in ' . __FILE__ . ' on line ' . __LINE__);
            $this->show_errors ? trigger_error($xw_mysql_str[3], E_USER_WARNING) : null;
        }
        else if (!$this->dbh)
        {
            $this->register_error($xw_mysql_str[4] . ' in ' . __FILE__ . ' on line ' . __LINE__);
            $this->show_errors ? trigger_error($xw_mysql_str[4], E_USER_WARNING) : null;
        }
        else if (!@mysql_select_db($dbname, $this->dbh))
        {
            if (!$str = @mysql_error($this->dbh))
                $str = $xw_mysql_str[5];

            $this->register_error($str . ' in ' . __FILE__ . ' on line ' . __LINE__);
            $this->show_errors ? trigger_error($str, E_USER_WARNING) : null;
        }
        else
        {
            $this->dbname = $dbname;
            $return_val = true;
        }

        return $return_val;
    }

    /**
     * 过滤字符串
     * 
     * @param type $str
     * @return type
     */
    function escape($str)
    {
        if (!isset($this->dbh) || !$this->dbh)
        {
            $this->connect($this->dbuser, $this->dbpassword, $this->dbhost);
            $this->select_db($this->dbname);
        }

        return mysql_real_escape_string(stripslashes($str));
    }

    /**
     *  设置数据库的输出编码
     * @param type $char_name
     */
    public function set_char($char_name)
    {
        $this->query("set names " . $char_name);
    }

    /**
     * 数据库时间函数
     * @return string
     */
    function sysdate()
    {
        return 'NOW()';
    }

    /**
     *  组合表名
     * 
     * @param type $table
     * @return type
     */
    public function get_table($table = '')
    {
        return $table !== '' ? $this->dbprefix . '_' . $table : $this->dbtable;
    }

    /**
     *  初始化表名
     * @param type $table
     */
    public function set_table($table)
    {
        $this->dbtable = !empty($this->dbprefix) ? $this->dbprefix . '_' . $table : $table;
    }

    /**
     * 
     * 执行sql
     * 
     * @param type $query
     * @return boolean
     */
    function query($query)
    {
        if ($this->num_queries >= 500)
        {
            $this->disconnect();
            $this->quick_connect($this->dbuser, $this->dbpassword, $this->dbname, $this->dbhost);
        }

        $return_val = 0;

        $this->flush();

        $query = trim($query);

        $this->func_call = "\$db->query(\"$query\")";

        $this->last_query = $query;

        // 记录共有多少个sql在执行
        $this->num_queries++;

        // 开始计算
        $this->timer_start($this->num_queries);

        // 得到执行的缓存
        if ($cache = $this->get_cache($query))
        {

            if ($this->use_trace_log)
            {
                $this->trace_log[] = $this->debug(false);
            }

            if (isset($benchmark))
            {

                Profiler::stop($benchmark);
            }

            return $cache;
        }

        if (!isset($this->dbh) || !$this->dbh)
        {
            $this->connect($this->dbuser, $this->dbpassword, $this->dbhost);
            $this->select_db($this->dbname);
        }
        if (Core::$profiling)
        {
            $benchmark = '"' . $query . '"';

            $benchmark = Profiler::start('Datebase_Query', $benchmark);
        }
        $this->result = @mysql_query($query, $this->dbh);


        if ($str = @mysql_error($this->dbh))
        {
            $is_insert = true;
            $this->register_error($str);
            $this->show_errors ? trigger_error($str, E_USER_WARNING) : null;
            return false;
        }

        // 执行相应的操作
        $is_insert = false;
        if (preg_match("/^(insert|delete|update|replace|truncate|drop|create|alter)\s+/i", $query))
        {
            $this->rows_affected = @mysql_affected_rows($this->dbh);

            // 初始插入ID号
            if (preg_match("/^(insert|replace)\s+/i", $query))
            {
                $this->insert_id = @mysql_insert_id($this->dbh);
            }

            // 返回受影响的条数
            $return_val = $this->rows_affected;
        }
        else
        {
            $i = 0;
            while ($i < @mysql_num_fields($this->result))
            {
                $this->col_info[$i] = @mysql_fetch_field($this->result);
                $i++;
            }

            // 保存结果
            $num_rows = 0;
            while ($row = @mysql_fetch_object($this->result))
            {
                $this->last_result[$num_rows] = $row;
                $num_rows++;
            }

            @mysql_free_result($this->result);

            // 最多连接数
            $this->num_rows = $num_rows;

            $return_val = $this->num_rows;
        }

        // 保存缓存
        $this->set_cache($query, $is_insert);

        $this->trace || $this->debug_all ? $this->debug() : null;

        $this->timer_update_global($this->num_queries);

        if ($this->use_trace_log)
        {
            $this->trace_log[] = $this->debug(false);
        }
        if (isset($benchmark))
        {

            Profiler::stop($benchmark);
        }
        return $return_val;
    }

    /**
     * 关闭数据库
     */
    function disconnect()
    {
        @mysql_close($this->dbh);
    }

    /**
     *  输出错误
     * @param type $err_str
     */
    function register_error($err_str)
    {
        //最新的错误
        $this->last_error = $err_str;

        // 得到所有的错误
        $this->captured_errors[] = array
            (
            'error_str' => $err_str,
            'query' => $this->last_query
        );
    }

    /**
     *  显示错误
     */
    function show_errors()
    {
        $this->show_errors = true;
    }

    /**
     *  隐藏错误
     */
    function hide_errors()
    {
        $this->show_errors = false;
    }

    /**
     * 当执行查询的时候清除缓存
     * 
     */
    function flush()
    {
        $this->last_result = null;
        $this->col_info = null;
        $this->last_query = null;
        $this->from_disk_cache = false;
    }

    /**
     *  得到一条数据中的某一个字段值
     * 
     * @param type $query
     * @param type $x
     * @param type $y
     * @return type
     */
    function get_var($query = null, $x = 0, $y = 0)
    {

        // 记录某一个方法被执行
        $this->func_call = "\$db->get_var(\"$query\",$x,$y)";

        // 如果不使用缓存就执行查询
        if ($query)
        {
            $this->query($query);
        }

        // 根据X Y 得到相应的缓存
        if ($this->last_result[$y])
        {
            $values = array_values(get_object_vars($this->last_result[$y]));
        }

        return (isset($values[$x]) && $values[$x] !== '') ? $values[$x] : null;
    }

    /**
     *  得到一条数据
     * @param type $query
     * @param type $output
     * @param type $y
     * @return type
     */
    function get_row($query = null, $output = ARRAY_A, $y = 0)
    {

        $this->func_call = "\$db->get_row(\"$query\",$output,$y)";

        if ($query)
        {
            $this->query($query);
        }

        /* 以对象的形式返回 */
        if ($output == OBJECT)
        {
            return $this->last_result[$y] ? $this->last_result[$y] : null;
        }
        /* 以数组的形式返回 */
        elseif ($output == ARRAY_A)
        {
            return $this->last_result[$y] ? get_object_vars($this->last_result[$y]) : null;
        }
        elseif ($output == ARRAY_N)
        {
            return $this->last_result[$y] ? array_values(get_object_vars($this->last_result[$y])) : null;
        }
        else
        {
            $this->print_error(" \$db->get_row(string query, output type, int offset) -- 输出类必须是: OBJECT, ARRAY_A, ARRAY_N");
        }
    }

    /**
     *   得到字段的值
     * @param type $query
     * @param type $x
     * @return type
     */
    function get_col($query = null, $x = 0)
    {

        $new_array = array();

        if ($query)
        {
            $this->query($query);
        }

        for ($i = 0; $i < count($this->last_result); $i++)
        {
            $new_array[$i] = $this->get_var(null, $x, $i);
        }

        return $new_array;
    }

    /**
     *  返回一个结果集 
     * @param type $query
     * @param type $output
     * @return null
     */
    function get_results($query = null, $output = ARRAY_A)
    {

        $this->func_call = "\$db->get_results(\"$query\", $output)";

        if ($query)
        {
            $this->query($query);
        }
      
        if ($output == OBJECT)
        {
            return $this->last_result;
        }
        elseif ($output == ARRAY_A || $output == ARRAY_N)
        {
            if ($this->last_result)
            {
                $i = 0;
                foreach ($this->last_result as $row)
                {

                    $new_array[$i] = (array)$row;
                    if ($output == ARRAY_N)
                    {
                        $new_array[$i] = array_values($new_array[$i]);
                    }

                    $i++;
                }

                return $new_array;
            }
            else
            {
                return null;
            }
        }
    }

    /**
     *  得到一个字段的详细信息
     * @param type $info_type
     * @param type $col_offset
     * @return type
     */
    function get_col_info($info_type = "name", $col_offset = -1)
    {
        if ($this->col_info)
        {
            if ($col_offset == -1)
            {
                $i = 0;
                foreach ($this->col_info as $col)
                {
                    $new_array[$i] = $col->{$info_type};
                    $i++;
                }
                return $new_array;
            }
            else
            {
                return $this->col_info[$col_offset]->{$info_type};
            }
        }
    }

    /**
     *  保存缓存
     * 
     * @param type $query
     * @param type $is_insert
     */
    function set_cache($query, $is_insert)
    {

        if ($this->cache_dir == FALSE)
        {
            return FALSE;
        }
        if (Core::$profiling)
        {
            $benchmark = '"' . $query . '"';

            $benchmark = Profiler::start('Datebase_Set_Cache', $benchmark);
        }

        // 缓存文件
        $cache_file = $this->cache_dir . DIRECTORY_SEPARATOR . md5($query);
        // 硬盘的缓存文件
        if ($this->use_disk_cache && ( $this->cache_queries && !$is_insert ) || ( $this->cache_inserts && $is_insert ))
        {
            if (!is_dir($this->cache_dir))
            {
                $this->register_error("找不到缓存文件: $this->cache_dir");
                $this->show_errors ? trigger_error("找不到缓存文件: $this->cache_dir", E_USER_WARNING) : null;
            }
            else
            {
                // 缓存一些查询的信息
                $result_cache = array
                    (
                    'col_info' => $this->col_info,
                    'last_result' => $this->last_result,
                    'num_rows' => $this->num_rows,
                    'return_value' => $this->num_rows,
                );


                error_log(serialize($result_cache), 3, $cache_file);
                if (isset($benchmark))
                {
                    // Stop the benchmark
                    Profiler::stop($benchmark);
                }
            }
        }
    }

    /**
     * 得到缓存
     * @param type $query
     * @return type
     */
    function get_cache($query)
    {
        if ($this->cache_dir == FALSE)
        {
            return FALSE;
        }
        if (Core::$profiling)
        {
            $benchmark = '"' . $query . '"';

            $benchmark = Profiler::start('Datebase_Get_Cache', $benchmark);
        }

        $cache_file = $this->cache_dir . '/' . md5($query);

        if (file_exists($cache_file) && $this->use_disk_cache)
        {

            if ((time() - filemtime($cache_file)) > ($this->cache_timeout * 3600))
            {
                unlink($cache_file);
            }
            else
            {
                $result_cache = unserialize(file_get_contents($cache_file));

                $this->col_info = $result_cache['col_info'];
                $this->last_result = $result_cache['last_result'];
                $this->num_rows = $result_cache['num_rows'];

                $this->from_disk_cache = true;

                $this->trace || $this->debug_all ? $this->debug() : null;
                if (isset($benchmark))
                {
                    // Stop the benchmark
                    Profiler::stop($benchmark);
                }
                return $result_cache['last_result'];
            }
        }
    }

    /**
     * 便于调试使用
     * @param type $mixed
     * @return type
     */
    function vardump($mixed = '')
    {

        ob_start();

        echo "<p><table><tr><td bgcolor=ffffff><blockquote><font color=000090>";
        echo "<pre><font face=arial>";

        if (!$this->vardump_called)
        {
            echo "<font color=800080><b>ezSQL</b> (v" . XW_VERSION . ") <b>Variable Dump..</b></font>\n\n";
        }

        $var_type = gettype($mixed);
        print_r(($mixed ? $mixed : "<font color=red>No Value / False</font>"));
        echo "\n\n<b>Type:</b> " . ucfirst($var_type) . "\n";
        echo "<b>Last Query</b> [$this->num_queries]<b>:</b> " . ($this->last_query ? $this->last_query : "NULL") . "\n";
        echo "<b>Last Function Call:</b> " . ($this->func_call ? $this->func_call : "None") . "\n";
        echo "<b>Last Rows Returned:</b> " . count($this->last_result) . "\n";
        echo "</font></pre></font></blockquote></td></tr></table>" . $this->donation();
        echo "\n<hr size=1 noshade color=dddddd>";

        $html = ob_get_contents();
        ob_end_clean();

        if ($this->debug_echo_is_on)
        {
            echo $html;
        }

        $this->vardump_called = true;

        return $html;
    }

    function dumpvar($mixed)
    {
        $this->vardump($mixed);
    }

    /**
     * 显示debug
     * @param type $print_to_screen
     * @return type
     */
    function debug($print_to_screen = true)
    {

        ob_start();

        echo "<blockquote>";

        if (!$this->debug_called)
        {
            echo "<font color=800080 face=arial size=2><b>ezSQL</b> (v" . XW_VERSION . ") <b>Debug..</b></font><p>\n";
        }

        if ($this->last_error)
        {
            echo "<font face=arial size=2 color=000099><b>Last Error --</b> [<font color=000000><b>$this->last_error</b></font>]<p>";
        }

        if ($this->from_disk_cache)
        {
            echo "<font face=arial size=2 color=000099><b>Results retrieved from disk cache</b></font><p>";
        }

        echo "<font face=arial size=2 color=000099><b>Query</b> [$this->num_queries] <b>--</b> ";
        echo "[<font color=000000><b>$this->last_query</b></font>]</font><p>";

        echo "<font face=arial size=2 color=000099><b>Query Result..</b></font>";
        echo "<blockquote>";

        if ($this->col_info)
        {

            echo "<table cellpadding=5 cellspacing=1 bgcolor=555555>";
            echo "<tr bgcolor=eeeeee><td nowrap valign=bottom><font color=555599 face=arial size=2><b>(row)</b></font></td>";


            for ($i = 0; $i < count($this->col_info); $i++)
            {
                echo "<td nowrap align=left valign=top><font size=1 color=555599 face=arial>{$this->col_info[$i]->type} {$this->col_info[$i]->max_length}</font><br><span style='font-family: arial; font-size: 10pt; font-weight: bold;'>{$this->col_info[$i]->name}</span></td>";
            }

            echo "</tr>";


            if ($this->last_result)
            {

                $i = 0;
                foreach ($this->get_results(null, ARRAY_N) as $one_row)
                {
                    $i++;
                    echo "<tr bgcolor=ffffff><td bgcolor=eeeeee nowrap align=middle><font size=2 color=555599 face=arial>$i</font></td>";

                    foreach ($one_row as $item)
                    {
                        echo "<td nowrap><font face=arial size=2>$item</font></td>";
                    }

                    echo "</tr>";
                }
            }
            else
            {
                echo "<tr bgcolor=ffffff><td colspan=" . (count($this->col_info) + 1) . "><font face=arial size=2>查询不到结果</font></td></tr>";
            }

            echo "</table>";
        }
        else
        {
            echo "<font face=arial size=2>查询不到结果</font>";
        }

        echo "</blockquote></blockquote>" . $this->donation() . "<hr noshade color=dddddd size=1>";

        $html = ob_get_contents();
        ob_end_clean();

        if ($this->debug_echo_is_on && $print_to_screen)
        {
            echo $html;
        }

        $this->debug_called = true;

        return $html;
    }

    /*     * ********************************************************************
     *  Naughty little function to ask for some remuniration!
     */

    function donation()
    {
        return "<font size=1 face=arial color=000000>If ezSQL has helped <a href=\"https://www.paypal.com/xclick/business=justin%40justinvincent.com&item_name=ezSQL&no_note=1&tax=0\" style=\"color: 0000CC;\">make a donation!?</a> &nbsp;&nbsp;<!--[ go on! you know you want to! ]--></font>";
    }

    /**
     * 时间助手
     * @return type
     */
    function timer_get_cur()
    {
        list($usec, $sec) = explode(" ", microtime());
        return ((float) $usec + (float) $sec);
    }

    function timer_start($timer_name)
    {
        $this->timers[$timer_name] = $this->timer_get_cur();
    }

    function timer_elapsed($timer_name)
    {
        return round($this->timer_get_cur() - $this->timers[$timer_name], 2);
    }

    function timer_update_global($timer_name)
    {
        if ($this->do_profile)
        {
            $this->profile_times[] = array
                (
                'query' => $this->last_query,
                'time' => $this->timer_elapsed($timer_name)
            );
        }

        $this->total_query_time += $this->timer_elapsed($timer_name);
    }

    /*     * ********************************************************************
     *    以数组的形式插入数据
     *    例如：
     *     $db_data = array('login'=>'jv','email'=>'jv@vip.ie', 'user_id' => 1, 'created' => 'NOW()');
     *
     *     $db->query("INSERT INTO users SET ".$db->get_set($db_data));
     *
     *     ...OR...
     *
     *     $db->query("UPDATE users SET ".$db->get_set($db_data)." WHERE user_id = 1");
     *
     * Output:
     *
     *     login = 'jv', email = 'jv@vip.ie', user_id = 1, created = NOW()
     */

    function get_set($parms)
    {
        $sql = '';
        foreach ($parms as $field => $val)
        {
            if ($val === 'true')
                $val = 1;
            if ($val === 'false')
                $val = 0;

            if ($val == 'NOW()')
            {
                $sql .= "$field = " . $this->escape($val) . ", ";
            }
            else
            {
                $sql .= "$field = '" . $this->escape($val) . "', ";
            }
        }

        return substr($sql, 0, -2);
    }

}
